import { Router, Request, Response } from 'express'
import { getDatabase } from '../database/init'
import { authenticateToken } from '../middleware/auth'

const router = Router()

// 所有统计路由都需要认证
router.use(authenticateToken)

// 获取任务统计概览
router.get('/overview', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    // 基础统计 - 只统计当前用户的任务
    const totalTasksResult = await db.query('SELECT COUNT(*) as count FROM tasks WHERE user_id = $1', [req.userId])
    const completedTasksResult = await db.query('SELECT COUNT(*) as count FROM tasks WHERE completed = true AND user_id = $1', [req.userId])
    const overdueTasksResult = await db.query(`
      SELECT COUNT(*) as count FROM tasks 
      WHERE completed = false 
        AND due_date IS NOT NULL 
        AND due_date::timestamp < NOW()
        AND user_id = $1
    `, [req.userId])
    
    const totalTasks = parseInt(totalTasksResult.rows[0].count)
    const completedTasks = parseInt(completedTasksResult.rows[0].count)
    const overdueTasks = parseInt(overdueTasksResult.rows[0].count)
    const pendingTasks = totalTasks - completedTasks
    
    const completionRate = totalTasks > 0 ? Math.round((completedTasks / totalTasks) * 100) : 0
    const overdueRate = totalTasks > 0 ? Math.round((overdueTasks / totalTasks) * 100) : 0
    
    res.json({
      success: true,
      data: {
        totalTasks,
        completedTasks,
        pendingTasks,
        overdueTasks,
        completionRate,
        overdueRate
      }
    })
  } catch (error) {
    console.error('获取统计概览失败:', error)
    res.status(500).json({ error: '获取统计概览失败' })
  }
})

// 获取分类分布统计
router.get('/category-distribution', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    const result = await db.query(`
      SELECT 
        category,
        COUNT(*) as total,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed,
        COUNT(CASE WHEN completed = false THEN 1 END) as pending
      FROM tasks 
      WHERE user_id = $1
      GROUP BY category
      ORDER BY total DESC
    `, [req.userId])
    
    const distribution = result.rows.map((row: any) => ({
      category: row.category,
      total: parseInt(row.total),
      completed: parseInt(row.completed),
      pending: parseInt(row.pending),
      completionRate: row.total > 0 ? Math.round((row.completed / row.total) * 100) : 0
    }))
    
    res.json({
      success: true,
      data: distribution
    })
  } catch (error) {
    console.error('获取分类分布失败:', error)
    res.status(500).json({ error: '获取分类分布失败' })
  }
})

// 获取优先级分布统计
router.get('/priority-distribution', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    const result = await db.query(`
      SELECT 
        priority,
        COUNT(*) as total,
        COUNT(CASE WHEN completed = true THEN 1 END) as completed,
        COUNT(CASE WHEN completed = false THEN 1 END) as pending
      FROM tasks 
      WHERE user_id = $1
      GROUP BY priority
      ORDER BY 
        CASE priority 
          WHEN 'P0' THEN 1 
          WHEN 'P1' THEN 2 
          WHEN 'P2' THEN 3 
          WHEN 'P3' THEN 4 
          ELSE 5 
        END
    `, [req.userId])
    
    const distribution = result.rows.map((row: any) => ({
      priority: row.priority,
      total: parseInt(row.total),
      completed: parseInt(row.completed),
      pending: parseInt(row.pending),
      completionRate: row.total > 0 ? Math.round((row.completed / row.total) * 100) : 0
    }))
    
    res.json({
      success: true,
      data: distribution
    })
  } catch (error) {
    console.error('获取优先级分布失败:', error)
    res.status(500).json({ error: '获取优先级分布失败' })
  }
})

// 获取时间趋势统计（最近7天/30天）
router.get('/time-trends', async (req: Request, res: Response) => {
  try {
    const days = parseInt(req.query.days as string) || 7
    const db = getDatabase()
    
    console.log('📊 时间趋势API调用:', { days, userId: req.userId })
    
    // 计算起始日期
    const startDate = new Date()
    startDate.setDate(startDate.getDate() - days)
    const startDateStr = startDate.toLocaleDateString('zh-CN', {
      year: 'numeric',
      month: '2-digit',
      day: '2-digit'
    }).replace(/\//g, '-') // 只取日期部分
    
    console.log('📅 查询参数:', { startDateStr, days })
    
    // 获取创建趋势 - 使用字符串日期比较
    const createdTrendsResult = await db.query(`
      SELECT 
        SUBSTRING(created_at::text, 1, 10) as date,
        COUNT(*) as created_count
      FROM tasks 
      WHERE SUBSTRING(created_at::text, 1, 10) >= $1 AND user_id = $2
      GROUP BY SUBSTRING(created_at::text, 1, 10)
      ORDER BY date
    `, [startDateStr, req.userId])
    
    console.log('📊 创建趋势查询结果:', createdTrendsResult.rows)
    
    // 获取完成趋势 - 使用字符串日期比较
    const completedTrendsResult = await db.query(`
      SELECT 
        SUBSTRING(updated_at::text, 1, 10) as date,
        COUNT(*) as completed_count
      FROM tasks 
      WHERE completed = true AND SUBSTRING(updated_at::text, 1, 10) >= $1 AND user_id = $2
      GROUP BY SUBSTRING(updated_at::text, 1, 10)
      ORDER BY date
    `, [startDateStr, req.userId])
    
    console.log('📊 完成趋势查询结果:', completedTrendsResult.rows)
    
    // 合并数据
    const trendsMap = new Map()
    
    // 初始化日期范围
    for (let i = days - 1; i >= 0; i--) {
      const date = new Date()
      date.setDate(date.getDate() - i)
      const dateStr = date.toLocaleDateString('zh-CN', {
        year: 'numeric',
        month: '2-digit',
        day: '2-digit'
      }).replace(/\//g, '-')
      trendsMap.set(dateStr, { date: dateStr, created: 0, completed: 0 })
    }
    
    // 填充创建数据
    createdTrendsResult.rows.forEach((row: any) => {
      const dateStr = row.date
      if (trendsMap.has(dateStr)) {
        trendsMap.get(dateStr).created = parseInt(row.created_count)
      }
    })
    
    // 填充完成数据
    completedTrendsResult.rows.forEach((row: any) => {
      const dateStr = row.date
      if (trendsMap.has(dateStr)) {
        trendsMap.get(dateStr).completed = parseInt(row.completed_count)
      }
    })
    
    const trends = Array.from(trendsMap.values())
    
    console.log('📊 最终趋势数据:', trends)
    console.log('📊 趋势数据长度:', trends.length)
    
    res.json({
      success: true,
      data: {
        period: `${days}天`,
        trends
      }
    })
  } catch (error) {
    console.error('获取时间趋势失败:', error)
    res.status(500).json({ error: '获取时间趋势失败' })
  }
})

// 获取效率分析
router.get('/efficiency', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    // 简化统计 - 先获取基本完成数据
    const completedTasksResult = await db.query(`
      SELECT COUNT(*) as total_completed FROM tasks WHERE completed = true AND user_id = $1
    `, [req.userId])
    
    const overdueCompletedResult = await db.query(`
      SELECT COUNT(*) as overdue_completed 
      FROM tasks 
      WHERE completed = true 
        AND due_date IS NOT NULL 
        AND updated_at > due_date::timestamp
        AND user_id = $1
    `, [req.userId])
    
    const totalWithDueDateResult = await db.query(`
      SELECT COUNT(*) as total_with_due_date 
      FROM tasks 
      WHERE completed = true AND due_date IS NOT NULL AND user_id = $1
    `, [req.userId])
    
    // 获取分类效率（按完成任务数排序）
    const categoryEfficiencyResult = await db.query(`
      SELECT 
        category,
        COUNT(*) as completed_count
      FROM tasks 
      WHERE completed = true AND user_id = $1
      GROUP BY category
      ORDER BY completed_count DESC
      LIMIT 5
    `, [req.userId])
    
    const totalCompleted = parseInt(completedTasksResult.rows[0]?.total_completed || 0)
    const overdueCompleted = parseInt(overdueCompletedResult.rows[0]?.overdue_completed || 0)
    const totalWithDueDate = parseInt(totalWithDueDateResult.rows[0]?.total_with_due_date || 0)
    
    const delayRate = totalWithDueDate > 0
      ? Math.round((overdueCompleted / totalWithDueDate) * 100)
      : 0
    
    // 计算每个分类的实际平均完成天数
    const categoryEfficiency = await Promise.all(
      categoryEfficiencyResult.rows.map(async (row: any) => {
        const avgDaysResult = await db.query(`
          SELECT 
            AVG(
              CASE 
                WHEN completed = true AND created_at IS NOT NULL AND updated_at IS NOT NULL 
                THEN EXTRACT(EPOCH FROM (updated_at::timestamp - created_at::timestamp))/86400 
              END
            ) as avg_days
          FROM tasks 
          WHERE user_id = $1 AND completed = true AND category = $2
        `, [req.userId, row.category])
        
        const avgDays = avgDaysResult.rows[0]?.avg_days
        
        return {
          category: row.category,
          avgDays: avgDays ? Math.round(parseFloat(avgDays) * 10) / 10 : null, // 如果没有数据返回null
          completedCount: parseInt(row.completed_count)
        }
      })
    )
    
    // 计算实际的平均完成天数
    const avgCompletionDaysResult = await db.query(`
      SELECT 
        AVG(
          CASE 
            WHEN completed = true AND created_at IS NOT NULL AND updated_at IS NOT NULL 
            THEN EXTRACT(EPOCH FROM (updated_at::timestamp - created_at::timestamp))/86400 
          END
        ) as avg_completion_days
      FROM tasks 
      WHERE user_id = $1 AND completed = true
    `, [req.userId])
    
    const avgCompletionDays = avgCompletionDaysResult.rows[0]?.avg_completion_days
    
    res.json({
      success: true,
      data: {
        avgCompletionDays: avgCompletionDays ? Math.round(parseFloat(avgCompletionDays) * 10) / 10 : null, // 如果没有数据返回null
        delayRate,
        categoryEfficiency,
        totalCompletedTasks: totalCompleted
      }
    })
  } catch (error) {
    console.error('获取效率分析失败:', error)
    res.status(500).json({ error: '获取效率分析失败' })
  }
})

// 获取标签使用统计
router.get('/tag-usage', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    // 获取当前用户所有任务的标签，然后在应用层处理
    const result = await db.query(`
      SELECT tags FROM tasks WHERE tags IS NOT NULL AND tags != '{}' AND user_id = $1
    `, [req.userId])
    
    const tagCounts = new Map()
    
    result.rows.forEach((row: any) => {
      if (row.tags && Array.isArray(row.tags)) {
        row.tags.forEach((tag: string) => {
          tagCounts.set(tag, (tagCounts.get(tag) || 0) + 1)
        })
      }
    })
    
    const tagUsage = Array.from(tagCounts.entries())
      .map(([tag, count]) => ({ tag, count }))
      .sort((a, b) => b.count - a.count)
      .slice(0, 10) // 取前10个最常用标签
    
    res.json({
      success: true,
      data: tagUsage
    })
  } catch (error) {
    console.error('获取标签使用统计失败:', error)
    res.status(500).json({ error: '获取标签使用统计失败' })
  }
})

// 获取连续完成天数
router.get('/streak', async (req: Request, res: Response) => {
  try {
    const db = getDatabase()
    
    // 获取最近30天的每日完成情况
    const dailyCompletionResult = await db.query(`
      SELECT 
        DATE(updated_at) as completion_date,
        COUNT(*) as completed_count
      FROM tasks 
      WHERE user_id = $1 
        AND completed = true 
        AND updated_at >= NOW() - INTERVAL '30 days'
      GROUP BY DATE(updated_at)
      ORDER BY completion_date DESC
    `, [req.userId])
    
    // 计算连续完成天数
    let currentStreak = 0
    let maxStreak = 0
    let tempStreak = 0
    
    const today = new Date()
    const yesterday = new Date(today)
    yesterday.setDate(yesterday.getDate() - 1)
    
    // 创建完成日期映射
    const completionDates = new Set(
      dailyCompletionResult.rows.map((row: any) => row.completion_date)
    )
    
    // 从今天开始往前检查连续天数
    for (let i = 0; i < 30; i++) {
      const checkDate = new Date(today)
      checkDate.setDate(checkDate.getDate() - i)
      const dateStr = checkDate.toISOString().split('T')[0]
      
      if (completionDates.has(dateStr)) {
        tempStreak++
        if (i === 0) {
          currentStreak = tempStreak
        }
        maxStreak = Math.max(maxStreak, tempStreak)
      } else {
        tempStreak = 0
      }
    }
    
    res.json({
      success: true,
      data: {
        currentStreak,
        maxStreak,
        totalCompletedDays: completionDates.size
      }
    })
  } catch (error) {
    console.error('获取连续天数失败:', error)
    res.status(500).json({ error: '获取连续天数失败' })
  }
})

export { router as statsRoutes }
